package bancosys.tec.utils.db.executor;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jmine.tec.utils.UtilsMessages;

import org.apache.log4j.Logger;

/**
 * @author Rafael Volpato
 * @created May 31, 2007
 */
public class PostgreSQLDbExecutor extends JdbcDbExecutor {

    private static final Logger LOG = Logger.getLogger(PostgreSQLDbExecutor.class);

    private static final String NO_UPPER_CASE = "--NoUpperCase\n";

    /**
     * SAM para modificar valores de colunas.
     * 
     * @author takeshi
     */
    private static interface ColumnValueModifier {

        /**
         * Modifica o valor de uma coluna
         * 
         * @param columnValue String
         * @return String
         */
        String apply(String columnValue);
    }

    private static final ColumnValueModifier NULL_MODIFIER = new ColumnValueModifier() {

        public String apply(String columnValue) {
            return columnValue;
        }
    };

    private static final ColumnValueModifier BOOLEAN_MODIFIER = new ColumnValueModifier() {

        public String apply(String columnValue) {
            return rewriteBoolean(columnValue);
        }
    };

    private final Map<String, ColumnValueModifier> modifiersMap = new HashMap<String, ColumnValueModifier>();

    /**
     * executa o drop das tabelas
     * 
     * @param conn Connection
     * @throws CouldNotExecuteException se não for possível executar
     */
    @Override
    protected void executeDrop(Connection conn) throws CouldNotExecuteException {
        LOG.info("Executando drop das tabelas, views e sequences");
        List<String> tables = new ArrayList<String>();
        Statement sqlSt;
        try {
            sqlSt = conn.createStatement();
            ResultSet rs = sqlSt.executeQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='public'");
            while (rs.next()) {
                tables.add(rs.getString(1));
            }
            sqlSt.close();
        } catch (SQLException e) {
            throw new CouldNotExecuteException(UtilsMessages.ORACLE_UNABLE_TO_QUERY_TABLES.create(), e);
        }

        for (String table : tables) {
            try {
                LOG.debug("Dropando tabela " + table);
                sqlSt = conn.createStatement();
                sqlSt.executeUpdate("DROP TABLE " + table + " CASCADE");
                sqlSt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        // drop sequence
        List<String> sequences = new ArrayList<String>();
        try {
            sqlSt = conn.createStatement();
            ResultSet rs = sqlSt.executeQuery("SELECT SEQUENCE_NAME FROM INFORMATION_SCHEMA.SEQUENCES WHERE SEQUENCE_SCHEMA='public'");
            while (rs.next()) {
                sequences.add(rs.getString(1));
            }
            sqlSt.close();
        } catch (SQLException e) {
            throw new CouldNotExecuteException(UtilsMessages.ORACLE_UNABLE_TO_QUERY_SEQUENCES.create(), e);
        }

        for (String sequence : sequences) {
            try {
                LOG.debug("Dropando sequence " + sequence);
                sqlSt = conn.createStatement();
                sqlSt.executeUpdate("DROP SEQUENCE " + sequence + " CASCADE");
                sqlSt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        LOG.info("Finalizando drop das tabelas e sequences");
    }

    public static final Pattern INSERT_SQL_PATTERN = Pattern.compile(
            "\\s*insert\\s+into\\s+([a-zA-Z_]+)\\s+[(]\\s*([a-zA-Z,_ \t]+)\\s*[)]\\s*values\\s*[(]\\s*(.+\\s*)[)]",
            Pattern.CASE_INSENSITIVE);

    /**
     * {@inheritDoc}
     * 
     * @see bancosys.tec.utils.db.executor.JdbcDbExecutor#executeSingleStatement(java.lang.String)
     */
    @Override
    public void executeSingleStatement(String statement) throws SQLException {
        // checking metadata
        boolean upper = false;
        if (statement.startsWith(NO_UPPER_CASE)) {
            upper = true;
        }
        final int valuesIndex = 3;
        Matcher matcher = INSERT_SQL_PATTERN.matcher(statement.replaceAll("\n", " "));
        if (matcher.find()) {
            String tableName = matcher.group(1);
            String[] cols = this.trimAll(SQLColumnParser.parse(matcher.group(2)));
            String[] vals = this.trimAll(SQLColumnParser.parse(matcher.group(valuesIndex)));

            ColumnValueModifier[] modifiers = this.getModifiers(tableName, cols);
            for (int i = 0; i < modifiers.length; i++) {
                ColumnValueModifier columnValueModifier = modifiers[i];
                vals[i] = columnValueModifier.apply(vals[i]);
            }
            super.executeSingleStatement((upper ? NO_UPPER_CASE : "") + " insert into " + tableName + " (" + toColumns(cols) + ") values ("
                    + toColumns(vals) + ")");
        } else {
            super.executeSingleStatement(statement);
        }
    }

    /**
     * Devolve o array de {@link ColumnValueModifier}
     * 
     * @param tableName String
     * @param cols array de String
     * @return array de {@link ColumnValueModifier}
     * @throws SQLException e
     */
    private ColumnValueModifier[] getModifiers(String tableName, String[] cols) throws SQLException {
        ColumnValueModifier[] allModifiers = new ColumnValueModifier[cols.length];
        for (int i = 0; i < cols.length; i++) {
            ColumnValueModifier mod = this.modifiersMap.get((tableName + "." + cols[i]));
            if (mod == null) {
                this.createModifiers(tableName, cols);
                mod = this.modifiersMap.get((tableName + "." + cols[i]));
                if (mod == null) {
                    throw new NullPointerException();
                }
            }
            allModifiers[i] = mod;
        }
        return allModifiers;
    }

    /**
     * Cria os modifiers para esta tabela e colunas
     * 
     * @param tableName String
     * @param cols String
     * @throws SQLException e
     */
    private void createModifiers(String tableName, String[] cols) throws SQLException {
        String select = "select " + toColumns(cols) + " from " + tableName;
        final ResultSet rs = this.getConnection().createStatement().executeQuery(select);
        try {
            ResultSetMetaData metaData = rs.getMetaData();
            for (int i = 0; i < cols.length; i++) {
                if ("bool".equals(metaData.getColumnTypeName(i + 1))) {
                    this.modifiersMap.put(tableName + "." + cols[i], BOOLEAN_MODIFIER);
                } else {
                    this.modifiersMap.put(tableName + "." + cols[i], NULL_MODIFIER);
                }
            }
        } finally {
            rs.close();
        }
    }

    /**
     * Reescreve um boolean que veio como 0 ou '0' para false e um boolean 1 ou '1' para true. Caso algum outro valor seja passado, lanca
     * uma excecao
     * 
     * @param string String
     * @return String
     */
    private static String rewriteBoolean(String string) {
        if ("0".equals(string) || "'0'".equals(string)) {
            return "false";
        } else if ("1".equals(string) || "'1'".equals(string)) {
            return "true";
        } else if ("null".equalsIgnoreCase(string)) {
            return string;
        }
        throw new IllegalArgumentException("weird boolean value to convert: " + string);
    }

    /**
     * Cria uma copia do array passado, fazendo um {@link String#trim()} em todas as entradas
     * 
     * @param split o array original
     * @return String
     */
    private String[] trimAll(String[] split) {
        String[] copy = new String[split.length];
        for (int i = 0; i < copy.length; i++) {
            copy[i] = split[i].trim();
        }
        return copy;
    }

    /**
     * Transforma um array de strings em uma string divididas por ,
     * 
     * @param cols String
     * @return String
     */
    private static String toColumns(String[] cols) {
        StringBuilder sb = new StringBuilder();
        for (int i = 0; i < cols.length;) {
            sb.append(cols[i]);
            if (++i < cols.length) {
                sb.append(", ");
            }
        }
        return sb.toString();
    }

    /**
     * @return o char delimitador
     */
    @Override
    public char getDelimiterChar() {
        return ';';
    }
}